SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[SP_TWSABA_REPO_Q90]                
/*--------------------------------------------------------*/                
/*---Empresa              : OFISIS S.A.     --*/                
/*---Cliente              : OFISIS      --*/                
/*---Sistema              : CALCULOS SALDOS Bancos  --*/                
/*---M+dulo               : Empresa      --*/                
/*---Programa             : CALCULA SALDO INICIAL  --*/                
/*---Script               : TMSABA_M.SQL    --*/                
/*---Nombre SP            : SP_TWSABA_REPO_Q90   --*/                
/*---Desarrollado por     : DCHIANG      --*/                
/*---Fecha Creacion    : 16/02/2005     --*/                 
/*---Base Datos           : SQLSERVER     --*/                
/*---Versi+n              : 8.0       --*/                
/*---Invoca a SP          :        --*/                
/*--------------------------------------------------------*/                
/*--------------------------------------------------------*/                
/*---Modificado 1 por     : DCHIANG      --*/                
/*---Fecha Modificaci+n   : 16/02/2005     --*/                 
/*---Detalle Modificaci+n : A SQL      --*/                
/*--------------------------------------------------------*/                            
/*--------------------------------------------------------*/                
/*---Comentarios          --*/                
/*---             --*/                
/*---             --*/                
/*--------------------------------------------------------*/                            
/*--------------------------------------------------------*/                
/* Drop Proc SP_TWSABA_REPO_Q90       --*/                
/* Grant All On SP_TWSABA_REPO_Q90 To Public   --*/                
/* SP_TWSABA_REPO_Q90 '01', 'OFISIS', "T1.CO_BANC = 'CRE'", '', '28/02/2007', 'O', '01/01/2006', '28/02/2007'*/                
/*--------------------------------------------------------*/                
@ISCO_EMPR TD_VC_002,                 
@ISCO_USUA TD_VC_008,                 
@ISCA_WH01 VARCHAR(125),                 
@ISCA_WH02 VARCHAR(130),                 
@ISFE_OPER VARCHAR(10),                 
@ISTI_FECH TD_VC_001, /*'O','S'*/                
@ISFE_INGR TD_VC_001, /*'O','D'*/                
@ISFE_EGRE TD_VC_001  /*'O','E','P','C'*/                
                
AS                
                
Create Table #TWPOSI_BANC_Q01_01                
( CO_EMPR varchar(02) NULL,                
CO_BANC varchar(03) NULL,                
NU_CNTA_BANC varchar(20),                
IM_INGR Numeric(16,4) null,                
IM_EGRE Numeric(16,4) null,                
IM_CHEQ_CART Numeric(16,4) null )                
                
Create Table #TWPOSI_BANC_Q01_02                
( CO_EMPR varchar(02) NULL,                
CO_BANC varchar(03) NULL,                
NU_CNTA_BANC varchar(20),                
IM_INGR Numeric(16,4) null,                
IM_EGRE Numeric(16,4) null,                
IM_CHEQ_CART Numeric(16,4) null )                
                
                
Create Table #TWSABA_REPO                
(CO_EMPR CHAR(02) NULL,                 
 CO_BANC VARCHAR(03) NULL,                
 NU_CNTA_BANC VARCHAR(20) NULL,                
 IM_SALD_INIC Numeric(16,4) NULL)                 
                
Create Table #TWSABA_REPO_AUXI(                 
 CO_EMPR VARCHAR(02) NULL,                
 CO_BANC VARCHAR(03) NULL,                
 NU_CNTA_BANC VARCHAR(20) NULL,                
 IM_SALD_INIC Numeric(16,4) NULL)                 
                
-- DECLARO VARIABLES DE TRABAJO                
                
DECLARE                
@VSCO_USUA TD_VC_008,                
@VSCA_WHER VARCHAR(255),                
@VSCA_WH01 VARCHAR(125),                
@VSCA_WH02 VARCHAR(130),                
@VSFE_OPER TD_DT_001,                
@VDFE_OPER TD_DT_001,                
@VSTI_FECH TD_VC_001, /*'O','S'*/                
@VSFE_INGR TD_VC_001, /*'O','D'*/                
@VSFE_EGRE TD_VC_001, /*'O','E','P','C'*/                
@VSCA_WHA1 VARCHAR(200),                 
@VSCA_WHA2 VARCHAR(200)                
                
-- ASIGNO LAS VARIABLES DE TRABAJO CON LAS VARIABLES DE ENTRADA                
                
SELECT @VSCO_USUA = @ISCO_USUA                
SELECT @VDFE_OPER = Convert(Datetime, @ISFE_OPER, 103)                
SELECT @VSTI_FECH = @ISTI_FECH                 
SELECT @VSFE_INGR = @ISFE_INGR                 
SELECT @VSFE_EGRE = @ISFE_EGRE                 
Select @VSCA_WH01 = ''                
Select @VSCA_WH02 = ''                
                
If Ltrim(Rtrim(@ISCA_WH02)) != '' And Ltrim(Rtrim(@ISCA_WH02)) is not null                
   Begin                
     Select @VSCA_WH02 = ' And T1.CO_BANC = ''' + @ISCA_WH02 + '''  '                
     Select @VSCA_WHA2 = ' And TMCNTA_BANC.CO_BANC = ''' + @ISCA_WH02 + '''  '                
   End               
                 
                
If Ltrim(Rtrim(@ISCA_WH01))  != '' And Ltrim(Rtrim(@ISCA_WH01))  Is not Null                
   Begin                
     Select @VSCA_WH01 = ' And T1.CO_MONE = ''' + @ISCA_WH01 + '''  '                
     Select @VSCA_WHA1 = ' And TMCNTA_BANC.CO_MONE = ''' + @ISCA_WH01 + '''  '                
   End                
                
SELECT @VSCA_WHER = @VSCA_WHA2 + @VSCA_WHA1                
                
-- INVOCA PROCEDURE PARA CARGA DE TABLAS TEMPORALES (SALDOS)                
                
EXECUTE SP_TWSABA_REPO_Q91 @VDFE_OPER, @VSTI_FECH, @VSFE_INGR, @VSFE_EGRE                
                
-- INVOCA PROCEDURE PARA CARGA DE TABLAS TEMPORALES (MOVIMIENTOS)                
                
EXECUTE SP_TCMOVI_BANC_Q01 @VSCO_USUA, @VSCA_WHER, @VDFE_OPER, @VSFE_INGR, @VSFE_EGRE                
                
                
Execute ('SELECT T1.CO_BANC, T7.DE_NOMB_BANC, T1.NU_CNTA_BANC AS BCO_CNTA, T1.CO_MONE AS COD_MONEDA, T2.IM_SALD_INIC AS SALDO_INICIAL, T3.IM_INGR AS INGRESO, T3.IM_EGRE AS EGRESO,                
   T2.IM_SALD_INIC + T3.IM_INGR - T3.IM_EGRE AS SALDO_LIBROS, T3.IM_CHEQ_CART AS CHEQUES_CARTERA,                 
   T1.SA_PROV_DEBE AS DEBE, T1.SA_PROV_HABE AS HABER,T2.IM_SALD_INIC + T3.IM_INGR - T3.IM_EGRE + T3.IM_CHEQ_CART + T1.SA_PROV_DEBE - T1.SA_PROV_HABE AS SALDO_DISPONIBLE                 
   FROM TMCNTA_BANC T1, #TWSABA_REPO T2, #TWPOSI_BANC_Q01_02 T3, TMEMPR T5, TMPARA_TESO T6, TTBANC_FINA T7                 
   WHERE 1=1                 
    AND T1.CO_EMPR = ''' + @ISCO_EMPR + '''  '+@VSCA_WH01 + @VSCA_WH02 +                
    'AND T1.CO_EMPR = T2.CO_EMPR                  
    AND T1.CO_BANC = T2.CO_BANC                 
    AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC                 
    AND T1.CO_EMPR = T3.CO_EMPR                
    AND T1.CO_BANC = T3.CO_BANC                
    AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC                
    AND T1.CO_EMPR = T5.CO_EMPR                
    AND T1.CO_EMPR = T6.CO_EMPR                
    AND T7.CO_BANC = T1.CO_BANC                
    AND EXISTS ( SELECT CO_EMPR  FROM  TTUSER_CNBA T4                
    Where T1.CO_EMPR = T4.CO_EMPR                
    AND T1.CO_BANC  = T4.CO_BANC                
    AND T1.NU_CNTA_BANC = T4.NU_CNTA_BANC                
    AND T4.CO_USUA = ''' + @VSCO_USUA + '''                
    AND T4.ST_REPO = ''S'')                
    ORDER BY T1.CO_MONE, T7.DE_NOMB_BANC, T1.NU_CNTA_BANC ' )                
               
                
Execute (' Select  T1.CO_MONE AS COD_MONEDA,isnull(SUM(T2.IM_SALD_INIC),''0.00'') AS SALDO_INICIAL,                 
 isnull(SUM(T3.IM_INGR),''0.00'') AS INGRESO,                
 isnull(SUM(T3.IM_EGRE),''0.00'') AS EGRESO,                
 isnull(SUM(T2.IM_SALD_INIC + T3.IM_INGR - T3.IM_EGRE),''0.00'') AS SALDO_LIBROS,                
 isnull(SUM(T3.IM_CHEQ_CART),''0.00'') AS CHEQUES_CARTERA,                
 isnull(SUM(T1.SA_PROV_DEBE),''0.00'') AS DEBE,                
 isnull(SUM(T1.SA_PROV_HABE),''0.00'') AS HABER,                
 isnull(SUM(T2.IM_SALD_INIC + T3.IM_INGR - T3.IM_EGRE + T3.IM_CHEQ_CART + T1.SA_PROV_DEBE - T1.SA_PROV_HABE),''0.00'') AS SALDO_DISPONIBLE                
 FROM TMCNTA_BANC T1, #TWSABA_REPO T2, #TWPOSI_BANC_Q01_02 T3, TMEMPR T5, TMPARA_TESO T6, TTBANC_FINA T7                
 WHERE 1=1                
         AND T1.CO_EMPR = ''' + @ISCO_EMPR + '''  '+@VSCA_WH01 + @VSCA_WH02 +                
        'AND T1.CO_EMPR = T2.CO_EMPR                  
  AND T1.CO_BANC = T2.CO_BANC                
  AND T1.NU_CNTA_BANC = T2.NU_CNTA_BANC                
  AND T1.CO_EMPR = T3.CO_EMPR                
  AND T1.CO_BANC = T3.CO_BANC                
  AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC                
  AND T1.CO_EMPR = T5.CO_EMPR                
  AND T1.CO_EMPR = T6.CO_EMPR                
  AND T7.CO_BANC = T1.CO_BANC                
  AND EXISTS ( SELECT CO_EMPR FROM  TTUSER_CNBA T4                
  Where T1.CO_EMPR = T4.CO_EMPR            
  AND T1.CO_BANC  = T4.CO_BANC                
  AND T1.NU_CNTA_BANC = T4.NU_CNTA_BANC                
         AND T4.CO_USUA = ''' + @VSCO_USUA + '''                
  AND T4.ST_REPO = ''S'')                
  GROUP BY T1.CO_MONE                
  ORDER BY T1.CO_MONE ' )                
             
                
                
/*------------------------------ FIN ---------------------------------*/


GO
